package test.dao;

import test.pojo.User;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    /**
     * 根据id删除
     *
     * @param id 要删除的用户id
     * @return
     */
    public int delete(int id) {
        try {
            return DBTool.executeUpdate("delete from users where id = ?", new Object[]{id});
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据id修改用户的名称和年龄
     *
     * @param name
     * @param age
     * @param id
     * @return
     */
    public int update(String name, int age, int id) {
        try {
            return DBTool.executeUpdate("update users set name=?,age=? where id=?", new Object[]{name, age, id});
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询所有的用户
     *
     * @return
     */
    public List<User> queryAll() {
        return queryBySQL("select * from users");
    }

    public List<User> queryBySQL(String sql) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        List<User> list = new ArrayList<>();
        try {
            conn = DBTool.getConnection();
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            // 开始进行读取操作
            while (rs.next()) {
                User u = new User();// 创建对象
                // 读取数据 设置给对象的属性
                u.setId(rs.getInt("id"));
                u.setName(rs.getString("name"));
                u.setAge(rs.getInt("age"));
                list.add(u);// 将数据添加到集合
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBTool.close(conn, st, rs);
        }
        return list;
    }

    public User queryById(int id) {
        List<User> list = queryBySQL("select * from users where id=" + id);
        return list.size() > 0 ? list.get(0) : null;// 查询出最多一条数据
    }
}
